import { Database } from 'better-sqlite3';
import path from 'path';

class ExportConfigService {
  constructor() {
    this.dbPath = path.join(__dirname, '../../../data/system.db');
  }

  // 获取数据库实例
  getDatabase() {
    return new Database(this.dbPath);
  }

  // 查询export表数据
  async getExportConfigs(queryParams) {
    const db = this.getDatabase();
    try {
      // 查询export表
      let sql = `SELECT * FROM export`;
      const params = [];
      
      if (queryParams.groupName) {
        sql += ` WHERE export_name LIKE ?`;
        params.push(`%${queryParams.groupName}%`);
      }
      
      sql += ` ORDER BY export_name LIMIT ? OFFSET ?`;
      params.push(queryParams.pageSize);
      params.push((queryParams.pageNum - 1) * queryParams.pageSize);

      const exports = db.prepare(sql).all(...params);
      
      // 查询总记录数
      let countSql = `SELECT COUNT(*) as total FROM export`;
      const countParams = [];
      
      if (queryParams.groupName) {
        countSql += ` WHERE export_name LIKE ?`;
        countParams.push(`%${queryParams.groupName}%`);
      }
      
      const totalResult = db.prepare(countSql).get(...countParams);
      
      // 获取units对应的单位名称
      const unitCodes = [];
      exports.forEach(item => {
        if (item.units) {
          const units = item.units.split(',');
          unitCodes.push(...units);
        }
      });
      
      const unitMap = {};
      if (unitCodes.length > 0) {
        const uniqueCodes = [...new Set(unitCodes)];
        const unitsSql = `
          SELECT code, name 
          FROM subUnits 
          WHERE code IN (${uniqueCodes.map(() => '?').join(',')})
        `;
        const units = db.prepare(unitsSql).all(...uniqueCodes);
        units.forEach(unit => {
          unitMap[unit.code] = unit.name;
        });
      }
      
      // 转换数据格式
      const result = exports.map(item => {
        let units = [];
        if (item.units) {
          units = item.units.split(',').map(code => {
            return { code, name: unitMap[code] || code };
          });
        }
        
        return {
          id: item.export_id,
          groupName: item.export_name,
          viewableUnits: units.map(unit => unit.name),
          rawUnits: units // 保留原始code，用于编辑时回显
        };
      });
      
      return {
        list: result,
        total: totalResult.total
      };
    } finally {
      db.close();
    }
  }
}

const exportConfigService = new ExportConfigService();

// 导出API函数
export const getExportConfigs = async (queryParams) => {
  return await exportConfigService.getExportConfigs(queryParams);
}

// 可以在这里添加其他需要的API函数
// export const addExportConfig = async (data) => { ... }
// export const updateExportConfig = async (data) => { ... }
// export const deleteExportConfig = async (id) => { ... }

export default exportConfigService;